/*
 * The contents of this file are subject to the Mozilla Public License
 * Version 1.1 (the "License"); you may not use this file except in
 * compliance with the License. You may obtain a copy of the License at
 * http://www.mozilla.org/MPL/
 *
 * Software distributed under the License is distributed on an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
 * License for the specific language governing rights and limitations
 * under the License.
 * 
 * The Original Code is iSQL-Viewer, A Mutli-Platform Database Tool.
 *
 * The Initial Developer of the Original Code is iSQL-Viewer, A Mutli-Platform Database Tool.
 * Portions created by Mark A. Kobold are Copyright (C) 2000-2007. All Rights Reserved.
 *
 * Contributor(s): 
 *  Mark A. Kobold [mkobold <at> isqlviewer <dot> com].
 *  
 * If you didn't download this code from the following link, you should check
 * if you aren't using an obsolete version: http://www.isqlviewer.com
 */
package org.isqlviewer.sql;

import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.Format;
import java.text.ParseException;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Map;
import java.util.Vector;

/**
 * Utility class for dealing with JDBC oriented tasks.
 * <p>
 * None of the methods that deal with ResultSets will actually close the ResultSet when completed, a call to the method
 * first is called before and after each method is finished executing the defined function.
 * <p>
 * A small note about methods that use a the ResultSetViewer object as a parameter to the method is that if the
 * ResultSetViewer is null and an SQLException occurs an attempt will be made to add the respecting exception as a
 * warning to the given ResultSet object. So it recommended that if you want to know if an SQLException occured without
 * using the ResultSetViewer check for warnings on the ResultSet after method invocation.
 * 
 * @author Markus A. Kobold &lt;mkobold at sprintpcs dot com&gt;
 */
public final class JdbcUtilities {

    // private final static String RESULT_RESERVED_CHARS = "srctfzwCST";

    private JdbcUtilities() {

        // nothing to do or see here.
    }

    /**
     * @return
     */
    public synchronized static Map<String, Object> extractMetadata(DatabaseMetaData dmd) {

        Class< ? extends DatabaseMetaData> c = dmd.getClass();
        Method[] methods = c.getMethods();
        Hashtable<String, Object> properties = new Hashtable<String, Object>(methods.length);
        Object[] p = new Object[0];

        for (int i = 0; i < methods.length; i++) {
            String methodName = methods[i].getName();
            try {
                if (methodName.startsWith("get")) {
                    Class returnType = methods[i].getReturnType();
                    methodName = methodName.substring(3);
                    if (returnType.isPrimitive() || returnType == String.class) {
                        Object methodResult = methods[i].invoke(dmd, p);
                        properties.put(methodName, methodResult);
                    }
                }
            } catch (AbstractMethodError ame) {
                properties.put(methodName, "");
            } catch (IllegalArgumentException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
        return properties;
    }

    /**
     * Checks if the ResultSet is deemed updatable.
     * <p>
     * Basically if the ResultSet has the UPDATABLE concurrency and is not of the FORWARD_ONLY type this method will
     * return true, IF the driver decides to throw exception when attempting to query this information this method will
     * always return false.
     * 
     * @see ResultSet#getConcurrency()
     * @see ResultSet#getType()
     * @param rs to check for updatability.
     * @return true if the ResultSet should support updates.
     */
    public static boolean isUpdatable(ResultSet rs) {

        try {
            int concur = rs.getConcurrency();
            int type = rs.getType();
            boolean updatable = (concur == ResultSet.CONCUR_UPDATABLE);
            boolean scrollable = (type == ResultSet.TYPE_SCROLL_INSENSITIVE || type == ResultSet.TYPE_SCROLL_SENSITIVE);
            return updatable && scrollable;
        } catch (Throwable t) {
            return false;
        }
    }

    /**
     * Helper method for determining if a given type is one of the date SQL types.
     * <p>
     * If the type equals DATE,TIME,TIMESTAMP then this will return true otherwise false.
     * 
     * @see Types
     * @param type SQL-Type to check
     * @return true if the given type represents Date objects
     */
    public static boolean isDateType(int type) {

        switch (type) {
            case Types.DATE :
            case Types.TIME :
            case Types.TIMESTAMP :
                return true;
            default :
                return false;
        }
    }

    /**
     * Helper method for determining if a given type is one of the String SQL types.
     * <p>
     * If the type equals CHAR,VARCHAR,LONGVARCHAR then this will return true otherwise false.
     * 
     * @see Types
     * @param type SQL-Type to check
     * @return true if the given type represents String objects
     */
    public static boolean isStringType(int type) {

        switch (type) {
            case Types.CHAR :
            case Types.LONGVARCHAR :
            case Types.VARCHAR :
                return true;
            default :
                return false;
        }
    }

    /**
     * Helper method for determining if a given type is one of the Boolean SQL types.
     * <p>
     * If the type equals BIT, BOOLEAN then this will return true otherwise false.
     * 
     * @see Types
     * @param type SQL-Type to check
     * @return true if the given type represents boolean objects
     */
    public static boolean isBooleanType(int type) {

        switch (type) {
            case Types.BIT :
            case Types.BOOLEAN :
                return true;
            default :
                return false;
        }
    }

    /**
     * Helper method for determining if a given type is one of the numerical SQL types.
     * <p>
     * If the type equals BIGINT,DECIMAL,DOUBLE,FLOAT,INTEGER,NUMERIC,REAL,SMALLINT, and TINYINT then this will return
     * true otherwise false.
     * 
     * @see Types
     * @param type SQL-Type to check
     * @return true if the given type represents numerical objects
     */
    public static boolean isNumberType(int type) {

        switch (type) {
            case Types.BIGINT :
            case Types.DECIMAL :
            case Types.DOUBLE :
            case Types.FLOAT :
            case Types.INTEGER :
            case Types.NUMERIC :
            case Types.REAL :
            case Types.SMALLINT :
            case Types.TINYINT :
                return true;
            default :
                return false;
        }
    }

    /**
     * Attempts to convert Object to it's standard type.
     * <p>
     * This method does make a fair attempt at conversion however it obviously can't convert everything. This method is
     * mainly to address working with the configurePreparedStatements as those method make the assumptions that the
     * objects are of type and simply casts them when appropriate.
     * <p>
     * Here is a rundown of what SQL Types goto what types. If the type is not listed here then object passed in will be
     * passed back.
     * <p>
     * Types :
     * <ul>
     * <li>CHAR, VARCHAR -&gt; {@link String}.
     * <li>TINYINT -&gt; {@link Byte}.
     * <li>SMALLINT -&gt; {@link Short}.
     * <li>INTEGER -&gt; {@link Integer}.
     * <li>BIGINT -&gt; {@link Long}.
     * <li>DOUBLE,FLOAT -&gt; {@link Double}.
     * <li>NUMERIC, DECIMAL -&gt; {@link BigDecimal}
     * <li>BOOLEAN, BIT -&gt; {@link Boolean}
     * <li>LONGVARBINARY, VARBINARY, BLOB -&gt; {@link ByteArrayBlob}
     * <li>CLOB, LONGVARCHAR -&gt; {@link ByteArrayClob}.
     * <li>DATE -&gt; {@link java.sql.Date}
     * <li>TIME -&gt; {@link Time}
     * <li>TIMESTAMP -&gt; {@link Timestamp}
     * </ul>
     * Anything not on the above list will not be converted to anything.
     * 
     * @see Types
     * @see #configurePreparedStatement(PreparedStatement, int, Object, int, boolean)
     * @param data to convert
     * @param type SQL type to convert to.
     * @param fmt object to use if parsing the data is required.
     * @return converted object.
     * @throws ParseException if using the format object parsing exception occurs.
     */
    public static Object convertValue(Object data, int type, Format fmt) throws ParseException, IOException {

        if (data == null && type != Types.NULL) {
            throw new IllegalArgumentException("null (" + getTypeforValue(type) + ")");
        } else if (data == null) {
            return null;
        }

        switch (type) {
            case Types.NULL :
                return null;

            case Types.CHAR :
            case Types.VARCHAR :
                return data.toString();

            case Types.TINYINT :
                if (data instanceof Number) {
                    if (data instanceof Byte) {
                        return data;
                    }
                    Number numb = (Number) data;
                    return new Byte(numb.byteValue());
                }
                String txt = data.toString().trim();
                if (txt.length() == 0) {
                    txt = "0";
                }

                try {
                    byte bite = Byte.parseByte(txt);
                    return new Byte(bite);
                } catch (Throwable t) {
                    if (fmt == null) {
                        throw new IllegalArgumentException(data.toString());
                    }

                    Number numb = (Number) fmt.parseObject(data.toString());
                    return new Byte(numb.byteValue());
                }

            case Types.SMALLINT :
                if (data instanceof Number) {
                    if (data instanceof Short) {
                        return data;
                    }
                    Number numb = (Number) data;
                    return new Short(numb.shortValue());
                }

                txt = data.toString().trim();
                if (txt.length() == 0) {
                    txt = "0";
                }

                try {
                    short shrt = Short.parseShort(txt);
                    return new Short(shrt);
                } catch (Throwable t) {
                    if (fmt == null) {
                        throw new IllegalArgumentException(data.toString());
                    }

                    Number numb = (Number) fmt.parseObject(data.toString());
                    return new Short(numb.shortValue());
                }

            case Types.INTEGER :
                if (data instanceof Number) {
                    if (data instanceof Integer) {
                        return data;
                    }
                    Number numb = (Number) data;
                    return new Integer(numb.intValue());
                }

                txt = data.toString().trim();
                if (txt.length() == 0) {
                    txt = "0";
                }

                try {
                    int val = Integer.parseInt(txt);
                    return new Integer(val);
                } catch (Throwable t) {
                    if (fmt == null) {
                        throw new IllegalArgumentException(data.toString().trim());
                    }

                    Number numb = (Number) fmt.parseObject(data.toString());
                    return new Integer(numb.intValue());
                }

            case Types.BIGINT :
                if (data instanceof Number) {
                    if (data instanceof Long) {
                        return data;
                    }
                    Number numb = (Number) data;
                    return new Long(numb.longValue());
                }
                txt = data.toString().trim();
                if (txt.length() == 0) {
                    txt = "0";
                }

                try {
                    long val = Long.parseLong(txt);
                    return new Long(val);
                } catch (Throwable t) {
                    if (fmt == null) {
                        throw new IllegalArgumentException(data.toString());
                    }

                    Number numb = (Number) fmt.parseObject(data.toString());
                    return new Long(numb.longValue());
                }

            case Types.DOUBLE :
            case Types.FLOAT :
                if (data instanceof Number) {
                    if (data instanceof Double) {
                        return data;
                    }
                    Number numb = (Number) data;
                    return new Double(numb.doubleValue());
                }
                txt = data.toString().trim();
                if (txt.length() == 0) {
                    txt = "0";
                }

                try {
                    double val = Double.parseDouble(txt);
                    return new Double(val);
                } catch (Throwable t) {
                    if (fmt == null) {
                        throw new IllegalArgumentException(data.toString());
                    }

                    Number numb = (Number) fmt.parseObject(data.toString());
                    return new Double(numb.doubleValue());
                }

            case Types.NUMERIC :
            case Types.DECIMAL :
                if (data instanceof Number) {
                    if (data instanceof BigDecimal) {
                        return data;
                    }
                    Number numb = (Number) data;
                    return new BigDecimal(numb.doubleValue());
                }

                txt = data.toString().trim();
                if (txt.length() == 0) {
                    txt = "0";
                }

                try {
                    return new BigDecimal(txt);
                } catch (Throwable t) {
                    if (fmt == null) {
                        throw new IllegalArgumentException(data.toString());
                    }

                    Number numb = (Number) fmt.parseObject(data.toString());
                    return new BigDecimal(numb.doubleValue());
                }

            case Types.BOOLEAN :
            case Types.BIT :
                if (data instanceof Boolean) {
                    return data;
                }

                try {
                    return Boolean.valueOf(data.toString());
                } catch (Throwable t) {
                    if (fmt == null) {
                        throw new IllegalArgumentException(data.toString());
                    }

                    return fmt.parseObject(data.toString());
                }

            case Types.LONGVARBINARY :
            case Types.VARBINARY :
            case Types.BLOB :
                if (data instanceof byte[]) {
                    return new ByteArrayBlob((byte[]) data);
                } else if (data instanceof Blob) {
                    byte[] blob = null;
                    try {
                        Blob b = (Blob) data;
                        blob = b.getBytes(0, (int) b.length());
                    } catch (SQLException sqle) {
                        blob = data.toString().getBytes();
                    }
                    return new ByteArrayBlob(blob);
                } else if (data instanceof Clob) {
                    byte[] blob = null;
                    try {
                        Clob c = (Clob) data;
                        blob = c.getSubString(0, (int) c.length()).getBytes();
                    } catch (SQLException sqle) {
                        blob = data.toString().getBytes();
                    }
                    return new ByteArrayBlob(blob);
                } else {
                    if (fmt == null) {
                        return data.toString().getBytes();
                    }

                    return fmt.parseObject(data.toString());
                }
            case Types.CLOB :
            case Types.LONGVARCHAR :
                try {
                    if (data instanceof Clob) {
                        Clob clob = (Clob) data;
                        try {
                            return new ByteArrayClob(clob.getSubString(0, (int) clob.length()));
                        } catch (SQLException e) {
                            throw new ParseException(e.getMessage(), 0);
                        }
                    } else if (data instanceof char[]) {
                        return new ByteArrayClob((char[]) data);
                    } else if (data instanceof String) {
                        return new ByteArrayClob(data.toString());
                    } else if (data instanceof Blob) {
                        String c = null;
                        try {
                            Blob b = (Blob) data;
                            c = new String(b.getBytes(0, (int) b.length()));
                        } catch (SQLException sqle) {
                            c = data.toString();
                        }
                        return new ByteArrayClob(c);
                    } else {
                        if (fmt == null) {
                            return new ByteArrayClob(data.toString());
                        }

                        return fmt.parseObject(data.toString());
                    }
                } catch (IOException ioe) {
                    return null;
                }
            case Types.DATE :
                if (data instanceof Date) {
                    Date date = (Date) data;
                    return new java.sql.Date(date.getTime());
                } else if (data instanceof Number) {
                    Number numb = (Number) data;
                    return new java.sql.Date(numb.longValue());
                } else {
                    if (fmt == null) {
                        throw new IllegalArgumentException(data.toString());
                    }

                    Date date = (Date) fmt.parseObject(data.toString());
                    return new java.sql.Date(date.getTime());
                }

            case Types.TIME :
                if (data instanceof Time) {
                    return data;
                } else if (data instanceof Date) {
                    Date date = (Date) data;
                    return new Time(date.getTime());
                } else if (data instanceof Number) {
                    Number numb = (Number) data;
                    return new Time(numb.longValue());
                } else {
                    if (fmt == null) {
                        throw new IllegalArgumentException(data.toString());
                    }

                    Date date = (Date) fmt.parseObject(data.toString());
                    return new Time(date.getTime());
                }

            case Types.TIMESTAMP :
                if (data instanceof Time) {
                    return data;
                } else if (data instanceof Date) {
                    Date date = (Date) data;
                    return new Timestamp(date.getTime());
                } else if (data instanceof Number) {
                    Number numb = (Number) data;
                    return new Timestamp(numb.longValue());
                } else {
                    if (fmt == null) {
                        throw new IllegalArgumentException(data.toString());
                    }

                    Date date = (Date) fmt.parseObject(data.toString());
                    return new Timestamp(date.getTime());
                }

            default :
                return data;

        }

    }

    /**
     * Refelcts to get SQL Type constant by name.
     * <p>
     * Simple reflection utility to get the proper constant for the Type name.
     * 
     * @see Types
     * @see #getTypeforValue(int)
     * @param name valid field name in Types
     * @return int representing the proper constant.
     */
    public static int getTypeforName(String name) {

        try {
            Class c = Types.class;
            return c.getField(name.toUpperCase()).getInt(null);
        } catch (Exception e) {
            return Types.VARCHAR;
        }
    }

    /**
     * Reverse lookup of a type name based on the constant.
     * <p>
     * This will iterate through the declared fields of the Types.class and once the correct field int value match the
     * given type it will return the name of the field.
     * 
     * @see #getTypeforName(String)
     * @see Types
     * @param type to get Field name for.
     * @return name of the field of Types.class based on the given constant.
     */
    public static String getTypeforValue(int type) {

        try {
            Class c = Types.class;
            Field[] flds = c.getDeclaredFields();
            for (int i = 0; i < flds.length; i++) {
                Field f = flds[i];
                if (f.getInt(null) == type) {
                    return f.getName();
                }
            }
            return null;
        } catch (Exception e) {
            return null;
        }

    }

    /**
     * Returns a list of Strings of valid Java SQL types.
     * <p>
     * This method reflects through {
     * 
     * @link java.sql.Types } object to get the names of all the fields in that object and returns them as an array.
     * @param sorted determines to sort the list or not.
     * @return String[] list of Types fields by name.
     */
    public static String[] getSQLTypes(boolean sorted) {

        Vector<String> list = new Vector<String>();
        try {
            Class c = Types.class;
            Field[] types = c.getFields();
            for (int i = 0; i < types.length; i++)
                try {
                    list.add(types[i].getName());
                } catch (Exception e) {
                    continue;
                }
        } catch (Exception e) {
        }
        String[] data = list.toArray(new String[list.size()]);
        if (sorted) {
            Arrays.sort(data);
        }
        return data;
    }

    public static HashMap<String, Integer> getNativeTypeMap(DatabaseMetaData metaData) {

        HashMap<String, Integer> map = new HashMap<String, Integer>();
        ResultSet set = null;
        try {
            set = metaData.getTypeInfo();
            while (set.next()) {
                String name = set.getString("TYPE_NAME");
                Integer sqlType = new Integer(set.getInt("DATA_TYPE"));
                map.put(name, sqlType);
            }
        } catch (Throwable t) {
            map.clear();
        } finally {
            try {
                if (set != null) {
                    set.close();
                }
            } catch (Throwable t) {
            }
        }
        return map;
    }

    /**
     * Helper method to assist with creating insert commands for prepared statements.
     * <p>
     * This will create an SQL statement in the following format. <br>
     * INSERT INTO {Table} ({Colums[0],},.., {Columns[n]}) VALUES(?,..,?) <br>
     * This method will return null if an exception occurs.
     * 
     * @param Table Name of table you wish to insert into.
     * @param Columns List of column names to declare values for.
     * @return Generated SQL statement for use with creating prepared statements.
     */
    public static String generatePreparedInsertRequest(String Table, String[] Columns) {

        if (Columns.length >= 1 && Table != null) {
            StringBuffer sql = new StringBuffer("INSERT INTO ");
            sql.append(Table);
            sql.append(" (");
            for (int i = 0; i < Columns.length - 1; i++) {
                sql.append(Columns[i]);
                sql.append(", ");
            }

            sql.append(Columns[Columns.length - 1]);
            sql.append(") VALUES (");

            for (int i = 0; i < Columns.length - 1; i++)
                sql.append("?, ");

            sql.append("?)");
            return sql.toString();
        }

        return null;
    }

    /**
     * Helper method to assist with creating update commands for prepared statements.
     * <p>
     * This will create an SQL statement in the following format. <br>
     * UPDATE {Table} SET {Colums[0]=?,},.., {Columns[n]=?} WHERE {whereClause} <br>
     * This method will return null if an exception occurs.
     * 
     * @param Table Name of table you wish to update.
     * @param Columns List of column names to update.
     * @param whereClause Standard SQL where clause for this update.
     * @return Generated SQL statement for use with creating prepared statements.
     */
    public static String generatePreparedUpdateRequest(String Table, String[] Columns, String whereClause) {

        try {
            StringBuffer sql = new StringBuffer("UPDATE ");
            sql.append(Table);
            sql.append(" SET (");
            for (int i = 0; i < Columns.length - 1; i++) {
                sql.append(Columns[i]);
                sql.append("=?, ");
            }

            sql.append(Columns[Columns.length - 1]);

            sql.append("=?)");
            if (whereClause != null && whereClause.trim().length() >= 1) {
                sql.append(" WHERE ");
                sql.append(whereClause);
            }
            return sql.toString();

        } catch (Throwable t) {
            return null;
        }
    }

    /**
     * Helper method to the other configurePreparedStatement method.
     * <p>
     * This method consists of a simple for loop that calls the other configurePreparedStatement method.
     * 
     * @see #configurePreparedStatement(PreparedStatement, int, Object, int, boolean)
     * @param ps PreparedStatement to configure.
     * @param data objects to set in the statement
     * @param type SQL-Types for each of the object.
     * @throws SQLException if error occurs setting the objects in the prepared statement.
     * @throws NullPointerException if data or type is null
     * @throws IllegalArgumentException if the data.length != type.length
     */
    public static void configurePreparedStatement(PreparedStatement ps, Object[] data, int[] type, boolean convert)
            throws SQLException {

        if (data == null || type == null) {
            throw new NullPointerException();
        }

        if (data.length != type.length) {
            throw new IllegalArgumentException();
        }

        for (int i = 0; i < data.length; i++) {
            configurePreparedStatement(ps, i + 1, data[i], type[i], convert);
        }

    }

    /**
     * Helper method set data parameters of a PreparedStatement.
     * <p>
     * This method does make the assumption that the given object is of the correct type as this method will simply cast
     * the object to the appropriate class based on the given SQL-Type. The logic as to what type should be what object
     * is detailed in the convert method, as it is recommend to call convert before this method as the successful
     * execution of convert will most likely ensure success of this method.
     * <p>
     * If the given value is null setNull(int,int) will be called on the statement and return immediately.
     * 
     * @see #convertValue(Object, int, Format)
     * @param index of the given parameter in the statement (index >= 1)
     * @param ps PreparedStatement to configure.
     * @param type for the given data object.
     * @throws SQLException if error occurs setting data.
     */
    public static void configurePreparedStatement(PreparedStatement ps, int index, Object value, int type,
            boolean convert) throws SQLException {

        if (value == null) {
            ps.setNull(index, type);
            return;
        }

        if (convert) {
            try {
                convertValue(value, type, null);
            } catch (ParseException pe) {
                throw new SQLException(pe.getMessage());
            } catch (IOException e) {
                throw new SQLException(e.getMessage());
            }
        }

        switch (type) {
            case Types.CHAR :
            case Types.VARCHAR :
                ps.setString(index, (String) value);
                break;

            case Types.TINYINT :
                ps.setByte(index, ((Number) value).byteValue());
                break;

            case Types.SMALLINT :
                ps.setShort(index, ((Number) value).shortValue());
                break;

            case Types.INTEGER :
                ps.setInt(index, ((Number) value).intValue());
                break;

            case Types.REAL :
                ps.setFloat(index, ((Number) value).floatValue());
                break;

            case Types.BIGINT :
                ps.setLong(index, ((Number) value).longValue());
                break;

            case Types.DOUBLE :
            case Types.FLOAT :
                ps.setDouble(index, ((Number) value).doubleValue());
                break;

            case Types.NUMERIC :
            case Types.DECIMAL :
                ps.setBigDecimal(index, (BigDecimal) value);
                break;

            case Types.BOOLEAN :
            case Types.BIT :
                ps.setBoolean(index, ((Boolean) value).booleanValue());
                break;

            case Types.BLOB :
                ps.setBlob(index, (Blob) value);
                break;

            case Types.CLOB :
                ps.setClob(index, (Clob) value);
                break;

            case Types.DATE :
                ps.setDate(index, (java.sql.Date) value);
                break;

            case Types.TIME :
                ps.setTime(index, (Time) value);
                break;

            case Types.TIMESTAMP :
                ps.setTimestamp(index, (Timestamp) value);
                break;

            case Types.LONGVARBINARY :
            case Types.VARBINARY :
                ByteArrayBlob blob = (ByteArrayBlob) value;
                ps.setBinaryStream(index, blob.getBinaryStream(), (int) blob.length());
                break;

            case Types.LONGVARCHAR :
                ByteArrayClob clob = (ByteArrayClob) value;
                ps.setCharacterStream(index, clob.getCharacterStream(), (int) clob.length());
                break;

            default :
                ps.setObject(index, value);
                break;

        }
    }

    public static Object getValueForType(ResultSet set, int type, int idx) throws SQLException {

        Object data = null;
        try {
            switch (type) {
                case Types.DATE :
                    data = set.getDate(idx);
                    break;
                case Types.TINYINT :
                    data = new Byte(set.getByte(idx));
                    break;
                case Types.SMALLINT :
                    data = new Short(set.getShort(idx));
                    break;
                case Types.TIMESTAMP :
                    data = set.getTimestamp(idx);
                    break;
                case Types.TIME :
                    data = set.getTime(idx);
                    break;
                case Types.INTEGER :
                    data = new Integer(set.getInt(idx));
                    break;
                case Types.DECIMAL :
                case Types.NUMERIC :
                    data = set.getBigDecimal(idx);
                    break;
                case Types.DOUBLE :
                case Types.FLOAT :
                case Types.REAL :
                    data = new Double(set.getDouble(idx));
                    break;
                case Types.BIGINT :
                    data = new Long(set.getLong(idx));
                    break;
                case Types.BIT :
                case Types.BOOLEAN :
                    data = new Boolean(set.getBoolean(idx));
                    break;
                case Types.LONGVARBINARY :
                case Types.VARBINARY :
                case Types.BINARY :
                    data = new ByteArrayBlob(set.getBytes(idx));
                    break;
                case Types.LONGVARCHAR :
                case Types.VARCHAR :
                case Types.CHAR :
                    data = set.getString(idx);
                    break;
                case Types.BLOB :
                    data = set.getBlob(idx);
                    break;
                case Types.CLOB :
                    data = set.getClob(idx);
                    break;
                case Types.REF :
                    data = set.getRef(idx);
                    break;
                case Types.ARRAY :
                    data = set.getArray(idx);
                    break;
                case Types.DISTINCT :
                    data = set.getObject(idx);
                    break;
                default :
                    data = set.getObject(idx);
                    break;

            }
        } catch (SQLException sqle) {
            // String[] p = new String[]{Integer.toString(idx), JdbcUtilities.getTypeforValue(type), sqle.getMessage()};
            String msg = sqle.toString();// BasicUtilities.getString("Resultset_Pull_Error", p);
            System.err.println(msg);
            throw sqle;
        } catch (Throwable t) {
            throw new RuntimeException(t);
        }

        try {
            if (set.wasNull()) {
                data = null;
            }
        } catch (Throwable t) {
        }
        return data;
    }
}